package dao;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.ColumnListHandler;
import org.apache.commons.dbutils.handlers.MapHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import com.liudecai.utils.common.CommonUtils;
import com.liudecai.utils.jdbc.TxQueryRunner;

import bean.MyClass;
import bean.StuTeaCla;
import bean.Teacher;

public class StuTeaClaDao {
	
	QueryRunner qr = null;
	public StuTeaClaDao() {
		super();
		qr = new TxQueryRunner();
	}

	/**
	 * 根据学生id号查出学生和教师信息
	 * MapHandler
	 * 	把结果集转换成一个Map
	 * 	以列名为key，以列值为值
	 * 	id:1
	 *  s_name:刘德财
	 *  .....
	 *  相同的列名会覆盖
	 * @param id
	 * @return
	 * @throws SQLException
	 */
	public StuTeaCla FindStuTeaByStuId (int id) throws SQLException {
		String sql = "select * from java_teacher t,java_student s where s.t_id=t.t_id and s.id=?";
//		String sql = "select s_id,s_name,s_gender,t_name,t_age,t_address from java_teacher t,java_student s where s.t_id=t.t_id and s.id=?";
		// 1.MapHandler 把结果集转换成一个Map
		Map<String, Object> map = qr.query(sql, new MapHandler(),id);
		// 2.利用ToBean将map转成两个对象
		StuTeaCla stutea = CommonUtils.MapToBean(map, StuTeaCla.class);
		Teacher teacher = CommonUtils.MapToBean(map, Teacher.class);
		// 3.两个对象建立关系
		stutea.setTeacher(teacher);
		return stutea;
	}
	
	public StuTeaCla FindStuTeaClaByStuId (int id) throws SQLException {
		String sql = "select * from java_student s, java_teacher t, java_class c where s.t_id=t.t_id and t.c_id=c.c_id and s.id=?";
		// 1.MapHandler 把结果集转换成一个Map
		Map<String, Object> map = qr.query(sql, new MapHandler(),id);
		// 2.利用ToBean将map转成两个对象
		StuTeaCla stutea = CommonUtils.MapToBean(map, StuTeaCla.class);
		Teacher teacher = CommonUtils.MapToBean(map, Teacher.class);
		MyClass myclass = CommonUtils.MapToBean(map, MyClass.class);
		// 3.三个对象建立关系
		stutea.setTeacher(teacher);
		stutea.setMyclass(myclass);
		return stutea;
	}
	
	/**查出多条信息
	 * @return
	 * @throws SQLException
	 */
	public List<StuTeaCla> FindManyStuTeaCla () throws SQLException {
		String sql = "select * from java_student s,java_teacher t where s.t_id=t.t_id";
		// MapListHandler 把结果集转换成List<Map>
		List<Map<String, Object>> maplist = qr.query(sql, new MapListHandler());
		// 用来装载生成的对象
		List<StuTeaCla> stutealist = new ArrayList<StuTeaCla>(); 
		for (Map<String, Object> map : maplist) {
			/*
			 * 把每个map映射成两个对象
			 * 再给他们建立关系
			 * 添加到stutealist中
			 */
			// 把map转成StuTeaCla对象
			StuTeaCla stutea = CommonUtils.MapToBean(map, StuTeaCla.class);
			// 把map转成Teacher对象
			Teacher teacher = CommonUtils.MapToBean(map, Teacher.class);
			// 两个对象建立关系
			stutea.setTeacher(teacher);
			stutealist.add(stutea);
		}
		return stutealist;
	}
	
	/**
	 * 获取所有学生姓名(单列查询)
	 * 使用ColumnListHandler
	 * @return
	 * @throws SQLException
	 */
	public List<Object> FindSutNames () throws SQLException {
		String sql = "select s_name from java_student";
		List<Object> stu_names = qr.query(sql, new ColumnListHandler<>());
		return stu_names;
	}
	
	/**
	 * 返回列的总记录数
	 * @return
	 * @throws SQLException
	 */
	public long count() throws SQLException {
		String sql = "select count(*) from java_student";
		Object count = qr.query(sql, new ScalarHandler<>());
		return (long) count;
	}
	
	
}
